'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    const { INTEGER, STRING, DECIMAL, DATE, TEXT, ENUM } = Sequelize;
    
    await queryInterface.createTable('reconciliation_results', {
      id: {
        type: INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      sales_order_id: {
        type: INTEGER,
        allowNull: false,
        comment: '销售订单ID'
      },
      bank_transaction_id: {
        type: INTEGER,
        allowNull: false,
        comment: '银行流水ID'
      },
      match_type: {
        type: ENUM('auto', 'manual'),
        allowNull: false,
        comment: '匹配类型'
      },
      match_confidence: {
        type: DECIMAL(5, 2),
        comment: '匹配置信度(0-100)'
      },
      match_algorithm: {
        type: STRING(50),
        comment: '匹配算法'
      },
      match_status: {
        type: ENUM('pending', 'confirmed', 'rejected'),
        defaultValue: 'pending',
        comment: '匹配状态'
      },
      matched_by: {
        type: INTEGER,
        comment: '匹配操作人'
      },
      matched_at: {
        type: DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
        comment: '匹配时间'
      },
      confirmed_by: {
        type: INTEGER,
        comment: '确认操作人'
      },
      confirmed_at: {
        type: DATE,
        comment: '确认时间'
      },
      notes: {
        type: TEXT,
        comment: '备注'
      }
    });

    // 添加索引
    // 检查索引是否已存在，避免重复创建
    try {
      await queryInterface.addIndex('reconciliation_results', ['match_status'], {
        name: 'reconciliation_results_idx_match_status',
        unique: false
      });
    } catch (error) {
      console.log('索引 reconciliation_results_idx_match_status 可能已存在:', error.message);
    }
    
    // 检查索引是否已存在，避免重复创建
    try {
      await queryInterface.addIndex('reconciliation_results', ['match_confidence'], {
        name: 'reconciliation_results_idx_confidence',
        unique: false
      });
    } catch (error) {
      console.log('索引 reconciliation_results_idx_confidence 可能已存在:', error.message);
    }

    // 添加唯一约束
    await queryInterface.addConstraint('reconciliation_results', {
      fields: ['sales_order_id', 'bank_transaction_id'],
      type: 'unique',
      name: 'uk_order_transaction'
    });

    // 添加外键约束
    await queryInterface.addConstraint('reconciliation_results', {
      fields: ['sales_order_id'],
      type: 'foreign key',
      name: 'fk_reconciliation_results_sales_order_id',
      references: {
        table: 'sales_orders',
        field: 'id'
      },
      onDelete: 'cascade',
      onUpdate: 'cascade'
    });
    
    await queryInterface.addConstraint('reconciliation_results', {
      fields: ['bank_transaction_id'],
      type: 'foreign key',
      name: 'fk_reconciliation_results_bank_transaction_id',
      references: {
        table: 'bank_transactions',
        field: 'id'
      },
      onDelete: 'cascade',
      onUpdate: 'cascade'
    });
  },

  down: async (queryInterface, Sequelize) => {
    // 先删除外键约束
    await queryInterface.removeConstraint('reconciliation_results', 'fk_reconciliation_results_sales_order_id');
    await queryInterface.removeConstraint('reconciliation_results', 'fk_reconciliation_results_bank_transaction_id');
    
    // 删除索引（如果存在）
    try {
      await queryInterface.removeIndex('reconciliation_results', 'reconciliation_results_idx_match_status');
    } catch (error) {
      console.log('索引 reconciliation_results_idx_match_status 删除失败:', error.message);
    }
    try {
      await queryInterface.removeIndex('reconciliation_results', 'reconciliation_results_idx_confidence');
    } catch (error) {
      console.log('索引 reconciliation_results_idx_confidence 删除失败:', error.message);
    }
    
    // 删除唯一约束
    await queryInterface.removeConstraint('reconciliation_results', 'uk_order_transaction');
    
    await queryInterface.dropTable('reconciliation_results');
  }
};